var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
/* GET users listing. */
router.get('/', function(req, res, next) {
    var page = req.query.page;
    var limit = req.query.limit;
    var db=new sqlite3.Database(path.join(__dirname, "../db/LH.s3db"));
    try{
        sqlStr = "SELECT \
            a_name , \
            s_activity.[a_no], \
            s_activity.[s_id] , \
            s_name , \
            class , \
            s_activity.[score] \
          FROM \
            ( \
              s_activity JOIN \
              activity ON s_activity.[a_no] = activity.[a_no] JOIN \
              student ON s_activity.[s_id] = student.[s_id] \
            ) \
          WHERE \
            activity.[m_id] = ?"
        if(req.query.edit=='1'){
            sqlStr = "UPDATE s_activity SET score = ? , status = 2 WHERE a_no IN (SELECT a_no FROM ( \
                      SELECT s_activity.[a_no] , a_name FROM s_activity JOIN activity ON s_activity.[a_no] = activity.[a_no] \
                    ) WHERE a_name = ? ) and s_id = ? ";
            db.all(sqlStr,[req.query.score,req.query.s_name,req.query.s_id],function(err){
                if(err!=null){
                    res.send('0');
                }
        });}
        else if(req.query.searchParams!=undefined){
            var enquiry = req.query.searchParams;
            console.log(enquiry);
            activity = (enquiry.activity=="")?"":" and a_name like '"+enquiry.activity+"%'";
            class_ = (enquiry.class=="")?"":" and class like '"+enquiry.class+"%'";
            s_id = (enquiry.ID =="")?"":" and s_activity.[s_id] like '"+enquiry.ID+"%'";
            s_name = (enquiry.name =="")?"":" and s_name like '"+enquiry.name+"%'";
            sqlStr+=activity+class_+s_id+s_name+" limit ?,?";
            db.all(sqlStr,[req.session.username,(page-1)*limit,limit],function(err,rows){
            table = {"code":0,"msg":"","count":rows.length,"data":""};
            table.data=rows;
            res.status(200).json(table);
            });
        }
        else if(req.query.edit==undefined){
            sqlStr+=" limit ?,?";
            db.all(sqlStr,[req.session.username,(page-1)*limit,limit],function(err,rows){
                table = {"code":0,"msg":"","count":rows.length,"data":""};
                table.data=rows;
                res.status(200).json(table);
            });
        }
        

    }catch(ex){
        console.log(ex);
}
});

module.exports = router;
